﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

/// <summary>
/// classDao 的摘要说明
/// </summary>
public class ClassDao
{
    //获取数据库连接串
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;

    /// <summary>
    /// 
    ///获取Info表中所有的记录
    /// </summary>
    /// <returns></returns>
    public static List<ClassInfo> FindAll(int start, int num)
    {
        List<ClassInfo> data = new List<ClassInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select * from tb_class limit " + start + "," + num;
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                ClassInfo classInfo = new ClassInfo();
                classInfo.Id = Convert.ToInt64(rdr["id"]);
                classInfo.ClassNum = Convert.ToString(rdr["class_num"]);
                classInfo.ClassName = Convert.ToString(rdr["class_name"]);

                data.Add(classInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }


    public static ClassInfo FindOne(long id)
    {
        ClassInfo data = new ClassInfo();

        MySqlConnection conn = new MySqlConnection(connStr);

        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "select * from tb_class where id = @id ";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id", id);

            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                data.Id = Convert.ToInt64(rdr["id"]);
                data.ClassNum = Convert.ToString(rdr["class_num"]);
                data.ClassName = Convert.ToString(rdr["class_name"]);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Find方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Find方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    //通过关键字查找
    public static List<ClassInfo> Find(string keyWord)
    {
        List<ClassInfo> data = new List<ClassInfo>();
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            string sql = "select * from tb_class where class_num=@class_num or class_name=@class_name";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@class_num", keyWord);
            cmd.Parameters.AddWithValue("@class_name", keyWord);
            //获取数据访问的游标
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                ClassInfo classInfo = new ClassInfo();
                classInfo.Id = Convert.ToInt64(rdr["id"]);
                classInfo.ClassNum = Convert.ToString(rdr["class_num"]);
                classInfo.ClassName = Convert.ToString(rdr["class_name"]);

                data.Add(classInfo);
            }
            rdr.Close();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        return data;
    }

    /// <summary>
    /// 更新一条记录
    /// </summary>
    /// <param name="data"></param>
    public static void Update(ClassInfo data)
    {

        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "update tb_class set class_num=@class_num,class_name=@class_name where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@class_num", data.ClassNum);
            cmd.Parameters.AddWithValue("@class_name", data.ClassName);
            cmd.Parameters.AddWithValue("@id", data.Id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Update方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Update方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

    }

    /// <summary>
    /// 删除一条记录
    /// </summary>
    /// <param name="id"></param>
    public static void Delete(long id)
    {
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "delete from tb_class where id=@id";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@id", id);
            //无返回结果集
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Delete方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Delete方法异常信息=======");
            throw (ex);
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 插入一条记录
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public static long Insert(ClassInfo data)
    {
        long insertId = 0;
        MySqlConnection conn = new MySqlConnection(connStr);
        try
        {
            conn.Open();
            //sql语句采用占位符，更加安全，可在一定程度上防止sql注入
            string sql = "insert into tb_class " +
                            "(class_num,class_name)" +
                            "values(@class_num,@class_name)";
            //构建一个sql的命令
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            //添加对应参数
            cmd.Parameters.AddWithValue("@class_num", data.ClassNum);
            cmd.Parameters.AddWithValue("@class_name", data.ClassName);

            //无返回结果集
            cmd.ExecuteNonQuery();
            //返回插入的记录的数据库Id
            insertId = cmd.LastInsertedId;
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine("======Insert方法异常信息=======");
            Debug.WriteLine(ex.Number + ":" + ex.Message);
            Debug.WriteLine("======Insert方法异常信息=======");
            throw (ex);

        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        return insertId;
    }
}